# -*- coding: utf-8 -*-
import pymysql
import json
from flask import Flask
from flask_cors import *
from markupsafe import escape
from flask import request
import sys

reload(sys)
sys.setdefaultencoding('utf8')


def mySQL(sqlString, parms):
    res = []
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "root", "softTest")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # 使用 execute()  方法执行 SQL 查询
    if parms != '':
        cursor.execute(sqlString, parms)
    else:
        cursor.execute(sqlString)
    # 使用 fetchmany() 方法获取数据.
    data = cursor.fetchmany(cursor.max_stmt_length)
    for val in data:
        temp = {"username": val[0], "id": val[1], "age": val[2], "sex": val[3]}
        res.append(temp)
    db.commit()
    db.close()
    return res


app = Flask(__name__)

CORS(app, supports_credentials=True)


@app.route('/getAllUser/', methods=["get"])
def getAllUser():
    if request.method == "GET":
        sqlString = "SELECT * FROM myinfo"
        res = mySQL(sqlString, "")
        return json.dumps(res, ensure_ascii=False)


@app.route('/getUserById/', methods=["get"])
def getUserById():
    if request.method == "GET":
        arg = request.args.get("Id", "")
        sqlString = "SELECT * FROM myinfo WHERE id = " + arg
        res = mySQL(sqlString, "")
        return json.dumps(res, ensure_ascii=False)


@app.route('/getUserByName/', methods=["get"])
def getUserByName():
    if request.method == "GET":
        arg = request.args.get("userName", "")
        sqlString = "SELECT * FROM myinfo WHERE username =%s"
        params = arg
        res = mySQL(sqlString, params)
        return json.dumps(res, ensure_ascii=False)


@app.route('/addUser/', methods=["post"])
def addUser():
    if request.method == "POST":
        av = request.form.get("userInfo", "")
        av = json.loads(av)
        # sqlString = "INSERT INTO myinfo(username,id,age,sex) VALUES(" + str(av['username']) + "," + str(av['id']) + "," + str(av['age']) + "," + str(av['sex']) + ")"
        # sqlString = "insert into myinfo(username,id,age,sex) values(%s,%s,%s,%s)"
        # params = (av['username'], av['id'], av['age'], av['sex'])
        sqlString = "insert into myinfo(username,age,sex) values(%s,%s,%s)"
        params = (av['username'], av['age'], av['sex'])
        # print (sqlString)
        res = mySQL(sqlString, params)
        return "增加用户成功"
        # print (av["id"])
        # return "成功"


@app.route('/updataUser/', methods=["post"])
def updataUser():
    if request.method == "POST":
        av = request.form.get("updatedUserInfo", "")
        print (av)
        av = json.loads(av)
        print (av)
        sqlString = "UPDATE myinfo SET age=%s,sex=%s WHERE id=%s"
        params = (av['age'], av['sex'], av['id'])
        res = mySQL(sqlString, params)
        return "更改数据成功"


@app.route('/delUserById/', methods=["get"])
def delUserById():
    print (request)
    if request.method == "GET":
        res = []
        arg = request.args.get("Id", "")
        sqlString = "delete from myinfo where id=" + arg
        print (sqlString)
        res = mySQL(sqlString, "")
        return "删除用户成功"
        # print (av["id"])
        # return "成功"


if __name__ == "__main__":
    app.run(debug=True)
